package com.wang.wx.utils;

import com.wang.wx.entity.EmailUser;
import lombok.extern.slf4j.Slf4j;

import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

@Slf4j
public class SqlScriptParserUtil {
    
    public static void main(String[] args) {
        EmailUser emailUser = parseInsertStatement("INSERT [dbo].[cdsgus] ([Name], [CardNo], [Descriot], [CtfTp], [CtfId], [Gender], [Birthday], [Address], [Zip], [Dirty], [District1], [District2], [District3], [District4], [District5], [District6], [FirstNm], [LastNm], [Duty], [Mobile], [Tel], [Fax], [EMail], [Nation], [Taste], [Education], [Company], [CTel], [CAddress], [CZip], [Family], [Version], [id]) VALUES (N'陈萌', N'', N'', N'OTH', N'010-116321', N'M', N'19000101', N'北京市海淀区苏州街3号大恒科技大厦北座6层', N'100080', N' ', N'', N'CHN', N'0', N'0', N'', N'', N'', N'', N'', N'10116', N'010-82808028', N'010-82828028-208', N'chenmeng@dist.com.cn', N'', N'', N'', N'', N'', N'', N'', N'0', N'2012-12-23 11:13:38', 2)");
        System.out.println(emailUser);
    }

    public static EmailUser parseInsertStatement(String line) {
        try {
            // INSERT INTO [dbo].[cdsgus] ([Name], [CardNo], [Descriot], ...) VALUES (N'陈萌', N'', N'', ...)
            // 如果不是以INSERT开头，则返回null
            if (!line.startsWith("INSERT")) {
                return null;
            }
            //截取VALUES后面的数据 (N'陈萌', N'', N'', ...)
            String valuesPart = line.substring(line.indexOf("VALUES") + 6).trim();
            // 去掉首尾括号N'陈萌', N'', N'', ...
            valuesPart = valuesPart.substring(1, valuesPart.length() - 1);
            
            // 替换 N'' 为空串N'陈萌', , , ...
            valuesPart = valuesPart.replaceAll("N''", "");

            // 按逗号分割
            String[] valuesArray = valuesPart.split(",");
            List<String> collect = Arrays.stream(valuesArray).map(s -> {
                s = s.replaceFirst("N'", "");
                if (s.equals(" ") || s.isEmpty()) {
                    return null;
                }
                return s.substring(0, s.length() - 1).trim();
            }).collect(Collectors.toList());

            //赋值操作
            return setValue(collect);
        }catch (Exception e) {
            log.error("解析SQL语句失败：" + line, e);
            return null;
        }
    }
    
    public static EmailUser setValue(List<String> collect) {
        if (collect.size() < 33 || collect.get(0) == null || collect.get(0).isEmpty()) {
            //如果长度不满足，或者没有姓名字段，则返回null
            return null;
        }
        EmailUser emailUser = new EmailUser();
        int i = 0;
        emailUser.setName(collect.get(i++));
        emailUser.setCardNo(collect.get(i++));
        emailUser.setDescriot(collect.get(i++));
        emailUser.setCtfTp(collect.get(i++));
        emailUser.setCtfId(collect.get(i++));
        emailUser.setGender(collect.get(i++));
        emailUser.setBirthday(collect.get(i++));
        emailUser.setAddress(collect.get(i++));
        emailUser.setZip(collect.get(i++));
        emailUser.setDirty(collect.get(i++));
        emailUser.setDistrict1(collect.get(i++));
        emailUser.setDistrict2(collect.get(i++));
        emailUser.setDistrict3(collect.get(i++));
        emailUser.setDistrict4(collect.get(i++));
        emailUser.setDistrict5(collect.get(i++));
        emailUser.setDistrict6(collect.get(i++));
        emailUser.setFirstNm(collect.get(i++));
        emailUser.setLastNm(collect.get(i++));
        emailUser.setDuty(collect.get(i++));
        emailUser.setMobile(collect.get(i++));
        emailUser.setTel(collect.get(i++));
        emailUser.setFax(collect.get(i++));
        if (collect.get(i) != null) {
            //因为邮箱库按照邮箱字段分表，如果邮箱为空，则分表可能无法正常工作
            emailUser.setEmail(collect.get(i++));
        } else {
            emailUser.setEmail("-");
            i++;
        }
        emailUser.setNation(collect.get(i++));
        emailUser.setTaste(collect.get(i++));
        emailUser.setEducation(collect.get(i++));
        emailUser.setCompany(collect.get(i++));
        emailUser.setCTel(collect.get(i++));
        emailUser.setCAddress(collect.get(i++));
        emailUser.setCZip(collect.get(i++));
        emailUser.setFamily(collect.get(i++));
        emailUser.setVersion(collect.get(i++));
        return emailUser;
    }
    
}